﻿using Janus.Cores.Repositories;
using Janus.Mes.DTO;
using Janus.Mes.POCO;
using Janus.Mes.Services.Interface;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace Tests.Janus.Mes.ServicesConsole
{
    public class SqlDataAdapterTest
    {
        IAssets services = ServiceFactory.Create<IAssets>();
        public void Test()
        {
            SqlConnection conn = new SqlConnection("Server=172.20.1.193;uid=sa;pwd=123456;database=jMes");
            string qry = @"select * from [Assets]";
            try
            {
                conn.Open();
                //create the DataAdapter
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(qry, conn);
                //Create a command command
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                cb.ConflictOption = ConflictOption.OverwriteChanges;
                da.UpdateCommand = cb.GetUpdateCommand();
                //Create the DataSet \
                DataSet ds = new DataSet();
                da.Fill(ds, "Assets");
                //get data table reference
                DataTable dt = ds.Tables["Assets"];
                var models = BatchInsert(2);
                var mydt = Table2ListHelper.ToDataTable<AssetsDTO>(models);
                ////mydt.TableName = "Assets";
                foreach (DataRow row in mydt.Rows)
                {
                    row["AssetsName"] = "update";
                    //dt.Rows.Add(row);
                    row.AcceptChanges();
                    row.SetModified();
                    DataRow newRow2 = dt.NewRow();
                    newRow2["AssetsCode"] = row["AssetsCode"];
                    newRow2["AssetsName"] = row["AssetsName"];
                    newRow2["SN"] = row["SN"];
                    newRow2["UseDate"] = row["UseDate"];
                    newRow2["UseLife"] = row["UseLife"];
                    newRow2["CreateDate"] = row["CreateDate"];
                    newRow2["UpdateDate"] = row["UpdateDate"];
                    newRow2["Creator"] = row["Creator"];

                    dt.Rows.Add(newRow2);
                    newRow2.AcceptChanges();
                    newRow2.SetModified();
                    newRow2["AssetsName"] = "update";
                }
                //add a row
                DataRow newRow = dt.NewRow();
                newRow["AssetsCode"] = "Wei" + DateTime.Now.ToString("ddHHmmss");
                newRow["AssetsName"] = "add Wei";
                newRow["SN"] = "jing";
                newRow["UseDate"] = DateTime.Now.AddDays(1);
                newRow["UseLife"] = 1;
                newRow["CreateDate"] = DateTime.Now.AddDays(1);
                newRow["UpdateDate"] = DateTime.Now.AddDays(1);
                newRow["Creator"] = "admin";
                dt.Rows.Add(newRow);

                //display the data
                //foreach (DataRow row in dt.Rows)
                //{
                //    Console.WriteLine("{0}{1}{2}", row["firstname"].ToString().PadRight(15), row["lastname"].ToString().PadLeft(25), row["city"]);
                //}
                //ds.Tables.Add(mydt);
                //da.UpdateCommand.ExecuteNonQuery();
                foreach (DataRow row in dt.Rows)
                {
                    foreach (SqlParameter p in da.UpdateCommand.Parameters)
                    {
                        p.Value = row[p.SourceColumn];
                    }
                    da.UpdateCommand.ExecuteNonQuery();
                }
                da.Update(ds, "Assets");
                ds.AcceptChanges();
               
            }
            catch (SqlException ex)
            {
                Console.WriteLine("The error :{0}", ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        private List<AssetsDTO> BatchInsert(int count = 50000)
        {
            var models = new List<AssetsDTO>();
            for (int i = 1; i <= count; i++)
            {
                var model = new AssetsDTO();
                model.AssetsCode = i.ToString();
                model.AssetsName = i + "Add" + DateTime.Now.ToString("yyyyMMddHH");
                model.Id = model.NewGuid2();
                model.SN = i.ToString();
                model.UseDate = DateTime.Now.AddDays(i);
                model.UseLife = i;
                model.CreateDate = DateTime.Now.AddDays(i);
                model.UpdateDate = DateTime.Now.AddDays(i);
                model.Creator = "admin";
                models.Add(model);
            }
            Stopwatch w = new Stopwatch();
            w.Start();
            var result = services.BatchInset(models);
            w.Stop();
            Console.WriteLine("BatchInsert ->{0},耗时：{1}毫秒", result, w.ElapsedMilliseconds);
            return models;
        }
    }
}
